﻿using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
   public class FundsFlowRepository:BaseRepository
    {
       //资金流列表
       public PagedList<FundsFlow_EX> QueryFundsFlowList(FundsFlowSearch search, PageView view)
       {
           //                          ,D.Branch as [BankName]
           string cols = @"A.[Id]
                          ,A.[MemberId]
                          ,D.RealName AS MemberName
                          ,B.Mobilenumber AS MemberPhone
                          ,A.[InCome]
                          ,A.[OutPay]
                          ,A.[BankId]
                         ,A.[BankName]

                          ,D.BankCardNo
                          ,A.[Balance]
                          ,A.[CreateTime]
                          ,A.[CreateUser]
                          ,A.[Status]
                          ,(CASE A.[Status] WHEN 1 THEN '审核通过' WHEN 0 THEN '待审核' WHEN -1 THEN '审核未通过' END) AS StatusName
                          ,(CASE A.[Status] WHEN 1 THEN '成功' WHEN 0 THEN '待审核' WHEN -1 THEN '失败' END) AS TenderStatusName
                          ,(CASE A.[Type] WHEN 1 THEN '充值' WHEN 2 THEN '提现' WHEN 3 THEN '投资' WHEN 4 THEN '收益' WHEN 5 THEN '本金返还' WHEN 6 THEN '提现手续费' END) AS TypeName
                          ,(CASE A.[Type] WHEN 1 THEN A.[InCome] WHEN 2 THEN A.[OutPay] WHEN 3 THEN A.[OutPay]  WHEN 4 THEN A.[InCome] WHEN 5 THEN A.[InCome] WHEN 6 THEN A.[OutPay] END) AS FundsAmt
                          ,A.[Type]
                          ,A.[ProjectId] ";
           string sqlWhere="";
           //if (!string.IsNullOrEmpty(search.Content))
           //{
           //    sqlWhere += " AND D.RealName LIKE '%" + search.Content + "%'";
           //}
           //if (!string.IsNullOrEmpty(search.Content))
           //{
           //    sqlWhere += " AND B.Mobilenumber LIKE '%" + search.Content + "%'";
           //}
           if(search.Type>0)
           {
               sqlWhere = string.Format("  AND A.[Type]={1}", search.Status, search.Type);//AND A.[Status]={0} 
           }
           if (!string.IsNullOrEmpty(search.Condition))
           {
               sqlWhere += " AND (D.RealName LIKE '%" + search.Condition + "%' OR B.Mobilenumber LIKE '%" + search.Condition + "%' ) ";
           }
           if (!string.IsNullOrEmpty(search.Sdate))
           {
               sqlWhere += " AND CONVERT(CHAR(10),A.[CreateTime],120)>= '" + search.Sdate.Trim() + "' ";
           }
           if (!string.IsNullOrEmpty(search.Edate))
           {
               sqlWhere += " AND CONVERT(CHAR(10),A.[CreateTime],120)<= '" + search.Edate.Trim() + "' ";
           }
           if (search.MemberId > 0)
           {
               sqlWhere += " AND A.[MemberId]=" + search.MemberId;
           }

           return base.PageGet<FundsFlow_EX>(view, cols,
               @" [U_FundsFlow] A  LEFT JOIN U_Member B 
                ON A.MemberId=B.ID LEFT JOIN U_Account C ON A.MemberId=C.MemberId
LEFT JOIN U_BankCard D ON C.ID=D.AccountId
"
               , sqlWhere
               , "A.ID DESC ",
               "");
       }
       //资金分页
       public List<FundsFlow_EX> QueryFundsFlowList(int pageindex, int memberId)
       {
           string sql = @"SELECT  
                        t.* from ( SELECT ROW_NUMBER() over (order by A.id desc) as row,A.[Id]
                          ,A.[MemberId]
                          ,D.RealName AS MemberName
                          ,B.Mobilenumber AS MemberPhone
                          ,A.[InCome]
                          ,A.[OutPay]
                          ,A.[BankId]
                         ,A.[BankName]
                        ,CONVERT(char(16),A.[CreateTime],120) as CreateTimeStr
                          ,D.BankCardNo
                          ,A.[Balance]
                          ,A.[CreateTime]
                          ,A.[CreateUser]
                          ,A.[Status]
                          ,(CASE A.[Status] WHEN 1 THEN '审核通过' WHEN 0 THEN '待审核' WHEN -1 THEN '审核未通过' END) AS StatusName
                          ,(CASE A.[Status] WHEN 1 THEN '成功' WHEN 0 THEN '待审核' WHEN -1 THEN '失败' END) AS TenderStatusName
                          ,(CASE A.[Type] WHEN 1 THEN '充值' WHEN 2 THEN '提现' WHEN 3 THEN '投资' WHEN 4 THEN '收益' WHEN 5 THEN '本金返还' WHEN 6 THEN '提现手续费' END) AS TypeName
                          ,(CASE A.[Type] WHEN 1 THEN A.[InCome] WHEN 2 THEN A.[OutPay] WHEN 3 THEN A.[OutPay]  WHEN 4 THEN A.[InCome] WHEN 5 THEN A.[InCome] WHEN 6 THEN A.[OutPay] END) AS FundsAmt
                          ,A.[Type]
                          ,A.[ProjectId] 
FROM [U_FundsFlow] A  LEFT JOIN U_Member B 
                ON A.MemberId=B.ID LEFT JOIN U_Account C ON A.MemberId=C.MemberId
LEFT JOIN U_BankCard D ON C.ID=D.AccountId  WHERE  A.[MemberId]=@MemberId) t
where t.row between 10*(" + pageindex + "-1)+1 and  10*(" + pageindex + "-1)+10 ";

           return base.Query<FundsFlow_EX>(sql, new { MemberId = memberId });
       }
       //资金分页 条数
       public int QueryFundsFlowCount(int memberId)
       {
           string sql = @"SELECT COUNT(A.[Id]) AS [COUNT]
                FROM [U_FundsFlow] A  LEFT JOIN U_Member B 
                ON A.MemberId=B.ID LEFT JOIN U_Account C ON A.MemberId=C.MemberId
                LEFT JOIN U_BankCard D ON C.ID=D.AccountId  WHERE  A.[MemberId]=@MemberId";
           return base.Get<int>(sql, new { MemberId = memberId });
       }

       //导出提现 待 汇款列表
       public List<FundsFlow_EX> QueryFundsExportList(FundsFlowSearch search)
       {
           //                          ,D.Branch as [BankName]
           string sql = @" SELECT A.[Id]
                          ,A.[MemberId]
                          ,D.RealName AS MemberName
                          ,B.Mobilenumber AS MemberPhone
                          ,B.Province
                          ,B.City   
                          ,A.[InCome]
                          ,A.[OutPay]
                          ,A.[BankId]
                          ,D.Branch as [BankName]
                          ,D.BranchDetail
                          ,D.BankCardNo
                          ,A.[Balance]
                          ,A.[CreateTime]
                          ,A.[CreateUser]
                          ,A.[Status]
                          ,(CASE A.[Status] WHEN 1 THEN '审核通过' WHEN 0 THEN '待审核' WHEN -1 THEN '审核未通过' END) AS StatusName
                        
                          ,(CASE A.[Type] WHEN 1 THEN '充值' WHEN 2 THEN '提现' WHEN 3 THEN '投资' WHEN 4 THEN '收益' END) AS TypeName
                          ,(CASE A.[Type] WHEN 1 THEN A.[InCome] WHEN 2 THEN A.[OutPay] WHEN 3 THEN A.[OutPay]  WHEN 4 THEN A.[InCome] END) AS FundsAmt
                          ,A.[Type]
                          ,A.[ProjectId] 
FROM  [U_FundsFlow] A  LEFT JOIN U_Member B 
                ON A.MemberId=B.ID LEFT JOIN U_Account C ON A.MemberId=C.MemberId
LEFT JOIN U_BankCard D ON C.ID=D.AccountId


";
          

           string sqlWhere = "";
           if (search.Type > 0)
           {
               sqlWhere = string.Format("  AND A.[Type]=2 AND A.[Status]=1 ");//AND A.[Status]={0} 
           }
           if (!string.IsNullOrEmpty(search.Condition))
           {
               sqlWhere += " AND (B.Name LIKE '%" + search.Condition + "%' OR B.Tel LIKE '%" + search.Condition + "%' ) ";
           }
           if (!string.IsNullOrEmpty(search.Sdate))
           {
               sqlWhere += " AND A.[CreateTime]>= '" + search.Sdate.Trim() + "' ";
           }
           if (!string.IsNullOrEmpty(search.Edate))
           {
               sqlWhere += " AND A.[CreateTime]<= '" + search.Edate.Trim() + "' ";
           }
           if (search.MemberId > 0)
           {
               sqlWhere += " AND A.[MemberId]=" + search.MemberId;
           }

           sql += sqlWhere;

           return base.Query<FundsFlow_EX>(sql, null);
       }

       //获取资金实体
       public FundsFlow_EX GetFundsFlow(int? id)
       {
           string sql = @" SELECT A.[Id]
                        ,A.[MemberId]
                        ,B.Name AS MemberName
                        ,B.Mobilenumber AS MemberPhone
                        ,A.[InCome]
                        ,A.[OutPay]
                        ,A.[BankId]
                        ,A.[BankName]
                        ,A.[Balance]
                        ,A.[CreateTime]
                        ,A.[CreateUser]
                        ,A.[Status]
                        ,(CASE A.[Status] WHEN 1 THEN '成功' WHEN 0 THEN '待审核' WHEN -1 THEN '失败' END) AS StatusName
                        ,A.[Type]
                        ,A.[ProjectId]
                        ,A.[Remark]
                        FROM
                        [U_FundsFlow] A  LEFT JOIN U_Member B 
                ON A.MemberId=B.ID WHERE A.[ID]=@ID";
           return base.Get<FundsFlow_EX>(sql, new { ID = id });
       }

       //审核保存 
       public int AuditSave(int? id, int status, string remark)
       {
           string sql = @" UPDATE U_FundsFlow SET Status=@STATUS,Remark=@REMARK,
                        AuditTime=@AUDITTIME,AuditUser=@AUDITUSER WHERE ID=@ID";
           return base.Excute(sql, new { ID = id, STATUS = status, REMARK = remark, AUDITTIME = DateTime.Now, AUDITUSER = "system" });
       }

       //账户资金
       public FundsFlow_EX GetAccountInfo(int memberId)
       {
           string sql = @"--TenderAmt 投资金额   FreezeAmt冻结金额  InComeAmt充值金额  EarningsAmt收益金额 TotalAmt总金额  	AvailableAmt可用金额
--EarningsAmt 收益 BenAmt返还本金
						SELECT 
						(T.TenderAmt-T.BenAmt)TenderAmt,T.FreezeAmt,
						(T.InComeAmt+T.EarningsAmt-T.WithdrawalsAmt)AS TotalAmt,
						((T.InComeAmt+T.EarningsAmt-T.WithdrawalsAmt)-((T.TenderAmt-T.BenAmt))-T.FreezeAmt)AS AvailableAmt
						,T.InComeAmt,T.TenderAmt TenderAmts,EarningsAmt
						FROM
						(    
						SELECT	
							ISNULL((SELECT SUM(A.OutPay) FROM U_FundsFlow A   WHERE A.[Type]=3 AND A.[Status]=1  AND A.[MemberId]=@MemberId),0) AS TenderAmt,
							ISNULL((SELECT SUM(A.OutPay) FROM U_FundsFlow A   WHERE A.[Type]=2 AND A.[Status]=0  AND A.[MemberId]=@MemberId),0) AS FreezeAmt,
							ISNULL((SELECT SUM(A.OutPay) FROM U_FundsFlow A   WHERE A.[Type]=2 AND A.[Status]=1  AND A.[MemberId]=@MemberId),0) AS WithdrawalsAmt,
							ISNULL((SELECT SUM(A.InCome) FROM U_FundsFlow A WHERE A.[Type]=1 AND A.[Status]=1    AND A.[MemberId]=@MemberId),0) AS InComeAmt,
							ISNULL((SELECT SUM(A.InCome) FROM U_FundsFlow A WHERE A.[Type]=4 AND A.[Status]=1    AND A.[MemberId]=@MemberId),0) AS EarningsAmt,
							ISNULL((SELECT SUM(A.OutPay) FROM U_FundsFlow A   WHERE A.[Type]=5 AND A.[Status]=0  AND A.[MemberId]=@MemberId),0) AS BenAmt
							)T ";
           return base.Get<FundsFlow_EX>(sql, new { MemberId = memberId });
       }


       /// <summary>
       /// 根据银行编码  取银行信息
       /// </summary>
       /// <param name="rb_BankId"></param>
       /// <returns></returns>
     /*  public U_YeeBankInfo GetYeePayBank(string rb_BankId)
       {
           string sql = @"SELECT [Id]
                          ,[BankCode]
                          ,[BankName]
                          FROM [U_YeeBankInfo] WHERE BankCode=@BankCode";
           return base.Get<U_YeeBankInfo>(sql, new { BankCode = rb_BankId });
       }*/

       //验证订单唯一性
       public int CheckOrderNo(string orderNo)
       {
           string sql = "SELECT COUNT(ID) FROM U_FundsFlow WHERE OrderNo=@OrderNo";
           return base.Get<int>(sql, new { OrderNo = orderNo });
       }

       //每个会员一天最多能提现次数
       public int CheckDrawalCount(int memberId)
       {
           string sql = @"SELECT COUNT(ID) AS COUNT FROM U_FundsFlow WHERE MemberId=@MemberId AND [TYPE]=2 AND CONVERT(char(10),CreateTime,120)=CONVERT(char(10),GETDATE(),120)";
           return base.Get<int>(sql, new { MemberId = memberId });
       }

       //根据投标的投标号来获取当前的资金记录，投标号是不能重复的。
       public U_FundsFlow GetFundsFlowByOrderNo(string orderNo)
       {
           string sql = @"select * from U_FundsFlow where OrderNo=@OrderNo";
           return base.Get<U_FundsFlow>(sql, new { OrderNo = orderNo });
       }

       //根据投标的投标号来获取当前的资金记录，投标号是不能重复的。
       public U_FundsFlow GetFundsFlowByOrderNo(string orderNo,int type)
       {
           string sql = @"select * from U_FundsFlow where OrderNo=@OrderNo and Type=@MyType";
           return base.Get<U_FundsFlow>(sql, new { OrderNo = orderNo,MyType = type });
       }

       //查询当前用户充值的记录
       public PagedList<U_FundsFlow> QueryFundsFlowList2(FundsFlowSearch search, PageView view)
       {
           string cols = @"Id,InCome,OutPay,BankName,CreateTime,Status,Type";
           string sqlWhere = "";
           if (search.Type == 0)//就是全部查询
           {
               sqlWhere += " and memberId=" + search.MemberId;
           }
           if (search.Type == 1) //充值
           {
               sqlWhere += " and Type=1 and memberId=" + search.MemberId;
           }
           if (search.Type==2)//提现，需要把手续费也查询出来
           {
               sqlWhere += " and Type in (2,6) and memberId=" + search.MemberId;
           }
           if (search.Type == 3)//众筹投标
           {
               sqlWhere += " and Type=3 and memberId=" + search.MemberId;
           }
           if (search.Type == 4)//债权投标
           {
               sqlWhere += " and Type=4 and memberId=" + search.MemberId;
           }
           if (search.Sdate!=null)
           {
               sqlWhere += " and CreateTime>='" + search.Sdate + "'";
           }
           if (search.Edate!=null)
           {
               sqlWhere += " and CreateTime<='" + search.Edate + "'";
           }
           return base.PageGet<U_FundsFlow>(view, cols, "U_FundsFlow", sqlWhere, "CreateTime Desc", "");
       }

       //查询当前用户的余额
       public decimal QueryBalance(int memberId)
       {
           string sql = @"select  ISNULL(sum(income)-sum(OutPay),0.00)  from [U_FundsFlow ] where MemberId=@MemberId and Status=1 and [type] in (1,2,6,4,5,7,8,11,12)";
           return base.Get<decimal>(sql, new {MemberId = memberId});
       }

       //获取当前项目所有投标的人和金额，以人分组
       public List<U_FundsFlow_EX> QueryInvestmemtByProjectId(int projectId)
       {
           string sql = @"select MemberId,SUM(OutPay)as SumOutPay,projectid from 
                        (
	                        select * from [U_FundsFlow ] where status=1 and type=4 and OutPay>0 and ProjectId=@ProjectId
                        )as table1
                        group by MemberId,projectid";
           return base.Query<U_FundsFlow_EX>(sql, new { ProjectId = projectId });
       }

       /// <summary>
       /// 查询当前用户的各种资金
       /// </summary>
       /// <param name="memberId"></param>
       /// <param name="type">1表示总资产，2表示余额，3表示累计充值，4表示累计投资，5表示累计收益</param>
       /// <returns></returns>
       public decimal QueryMoney(int memberId,int type)
       {
           string sql = "";
           switch (type)
           {
               case 1: //总资产
                   sql = @"select  ISNULL(sum(income)-sum(OutPay),0.00)  from [U_FundsFlow ] where MemberId=@MemberId and Status=1 and [type] in (1,2,6,5,7,8,11,12)";
                   break;
               case 2: //余额
                   sql = @"select  ISNULL(sum(income)-sum(OutPay),0.00)  from [U_FundsFlow ] where MemberId=@MemberId and Status=1 and [type] in (1,2,6,4,5,7,8,11,12)";
                   break;
               case 3: //累计充值
                   sql = @"select  ISNULL(sum(income),0.00)  from [U_FundsFlow ] where MemberId=@MemberId and Status=1 and [type] in (1)";
                   break;
               case 4: //累计投资
                   sql = @"select  ISNULL(sum(TenderAmt),0.00) from T_Investment where MemberId=@MemberId and Status=1";
                   break;
               case 5: //累计收益
                   sql = @"select  ISNULL(sum(income),0.00)  from [U_FundsFlow ] where MemberId=@MemberId and Status=1 and [type] in (5)";
                   break;
           }
           return base.Get<decimal>(sql, new { MemberId = memberId });
       }

    }
}
